#region imported Namespaces
using System;
using System.Data;
using SqlTypes = System.Data.SqlTypes;
using System.Collections;
using System.Collections.Specialized;
using System.Data.SqlClient;
using BO = PHSRAG.Insight21.BusinessObjects;
using Utility = PHSRAG.Utility;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using UserContext = PHSRAG.Insight21.Context.UserContext;
#endregion

namespace PHSRAG.Insight21.Purchasing
{
	#region UserOrderLineItems Class
	/// <summary>
	/// Business entity encapsulating the collection of line items.
	/// It holds the properties and behaviour to load and sort a line item
	/// collection. It supports indexing through which it exposes the line item
	/// contained in its line item collection.
	/// </summary>
	public class UserOrderLineItems: BO.TransactionalBusinessObject, IEnumerable
	{
		#region Constants

		private const string LineItemsKey				= "LineItemsKey_2877c49b-c62b-45e9-9aff-34572b9cdbe7";		
		private const int    FirstAgrument				= 0;
		
		//Data Column Names
		private const string RequistionNumberColumnName	= "RequisitionNumber";
		private const string VendorIDColumnName			= "VendorID";
		private const string VendorNameColumnName		= "VendorName";
		private const string VendorNumberColumnName		= "VendorNumber";

		//Stored Procedure Parameter Name
		private const string DomainUserIDParamName			= "@domainUserID";
		private const string OrderIDParamName				= "@orderID";
		private const string LineIDParamName				= "@lineID";
		private const string FundIDParamName				= "@fundID";
		private const string AccountIDParamName				= "@accountID";
		private const string AmountParamName				= "@amount";
		private const string CommentParamName				= "@comment";
		private const string RequestorParamName				= "@requestorName";
		private const string DomainUserID					= "@domainUserID";
		private const string OrderID						= "@orderID";

		//Stored Procedure Name
		private const string GetLineItemsSPName				= "GetUserOrderLineItems";
		private const string AddLineItemSPName				= "AddUserOrderLineItem";
		private const string UpdateLineItemSPName			= "UpdateUserOrderLineItem";
		private const string DeleteLineItemSPName			= "DeleteUserOrderLineItem";

		#endregion

		#region Enumerations (Private)
		// Enumerates the stored procedure parameters
		// for loading the line items
		private enum LoadLineItemsSPArguments
		{
			OrderID,
			DomainUserID,
			Count
		}
		#endregion

		#region Instance Variables
		private ArrayList lineItems = new ArrayList();
		private bool	sortAscending	= true;		
		// Keep this in sync with what the stored proc orders by.
		private string	sortColumn		= string.Empty;

		// Is used to check if the command object
		// for current line item class has been created.
		private bool isCommandCreated;

		private SqlCommand addCommand;
		private SqlCommand updateCommand;
		private int orderID;
		#endregion

		#region Instance Properties
		/// <summary>
		/// Get the key to be used in all caching operations for this instance of the object.
		/// </summary>
		public static string CacheKey	{ get { return LineItemsKey; } }
		/// <summary>
		/// Gets the count of the lineitems in the
		/// line items collection.
		/// </summary>
		public int Count
		{
			get{return lineItems.Count;}
		}
		/// <summary>
		/// Get the sort sequence.
		/// </summary>
		public bool SortAscending
		{
			get { return sortAscending; }
		}

		/// <summary>
		/// Get the name of the sort column.
		/// </summary>
		public string SortColumn
		{
			get { return sortColumn; }
		}
		#endregion
		
		#region Constructor
		/// <summary>
		/// Initializes the base class with the specified cache policy
		/// and this class with the dataset
		/// </summary>
		/// <param name="dataSet">dataset for initializing the datacontainer</param>
		/// <param name="cachePolicy">prevalent cache policy</param>
		public UserOrderLineItems(DataSet dataSet,ICachePolicy cachePolicy) :
			base(cachePolicy)
		{
			DataContainer = dataSet;
		}
		#endregion

		#region Public Methods
		/// <summary>
		/// Adds an empty row to the table containing line items, which is then
		/// loaded in the lineitems collection as a new line item. It creates and
		/// adds a new line item table only when it does not exist, in case when
		/// user order is a new one. 
		/// </summary>
		public void CreateLineItem()
		{
			if(Count == 0)
				CreateAndAddLineItemsTable();
			AddLineItemRow();
			LoadLineItemList();
		}		
		/// <summary>
		/// Deletes the lineitem from the database and from the table containing
		/// line item records. It checks the row state to determine if the row being deleted is 
		/// a new row, that is, it is not in the database. If its not in database, the stored procedure
		/// for deleting the record is not called. Accept changes is only called for rows loaded from
		/// database, so that deleted row is not used for loading the line items collection. After successful
		/// deletion of the row, the reports whose data becomes stale because of this deletion are uncached.
		/// </summary>
		/// <param name="index">index of the line item in the line item collection</param>
		public void DeleteLineItem(int index)
		{
			try 
			{
				DataRow lineItemRow = DataContainer.Tables[Constants.UserOrderDetailsLineItemsTableName].Rows[index];
				if(lineItemRow.RowState != DataRowState.Added)
				{
					Utility.DataAccess.ExecuteNonQuery(Utility.DataAccess.GetConnectionString(),
						DeleteLineItemSPName, Utility.DataAccess.BuildSqlParameterArray
						(DomainUserIDParamName, SqlDbType.Int, 
						((UserContext)CachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId,
						LineIDParamName, SqlDbType.Int, this[index].LineID));					
					lineItemRow.Delete();
					lineItemRow.AcceptChanges();
					UserOrderHelper.UnCacheDependentReports(CachePolicy);
				}
				else
					lineItemRow.Delete();
                LoadLineItemList();				
			}
			catch(Exception ex)
			{
				throw(new Exception(@"Error in deleting line item.", ex));
			}					
		}

		/// <summary>
		/// Uses the column name passed to set the sort order and sort column,
		/// and load the sorted collection of line items
		/// </summary>
		/// <param name="columnName">name of the current sort colummn</param>
		public void ProcessSort(string columnName)
		{
			sortAscending = (sortColumn == columnName) ? !sortAscending : sortAscending;
			sortColumn = columnName;
			LoadLineItemList();
		}

		/// <summary>
		/// Loops though the line item collection and validates
		/// the fund and account information of individual line items.
		/// </summary>
		/// <param name="connection">open connection object</param>
		public void ValidateLineItems(SqlConnection connection)
		{
			foreach(UserOrderLineItem lineItem in this)
			{
				lineItem.ValidateFundAndAccount(connection);
			}
		}	
		#endregion

		#region Transactional Business Object Methods
		/// <summary>
		/// This method loads the line items from the database for the specifed order id to 
		/// which the user has access and loads an arraylist with lineitem object. If no rows
		/// are returned a row is added to the line items datatable.The method requires two 
		/// parameters in the params object array in the given order:
		/// 1) OrderID
		/// 2) DomainUserID
		/// </summary>
		/// <param name="connectionString">connection string pointing to the database to get data from</param>
		/// <param name="args">arguments to load user order line items</param>
		public override void Load(string connectionString, params object[] args)
		{
			if (!args.Length.Equals((int)LoadLineItemsSPArguments.Count))
				throw new Exception(String.Format("User order line items load requires {0} parameters",
					((int)LoadLineItemsSPArguments.Count).ToString()));
			try
			{
				Utility.DataAccess.LoadDataSet(DataContainer, Constants.UserOrderDetailsLineItemsTableName,
					connectionString, GetLineItemsSPName, Utility.DataAccess.BuildSqlParameterArray(
					OrderID, SqlDbType.Int, (int)args[(int)LoadLineItemsSPArguments.OrderID],
					DomainUserID, SqlDbType.Int, (int)args[(int)LoadLineItemsSPArguments.DomainUserID]));

				if(DataContainer.Tables[Constants.UserOrderDetailsLineItemsTableName].Rows.Count.Equals(0))
                    AddLineItemRow();
                LoadLineItemList();
			}
			catch(Exception ex)
			{
				throw new Exception(@"Error in loading lineItems.", ex);
			}
		}

		/// <summary>
		/// Caches the UserOrderLineItems object to cache using the prevalent cache policy
		/// </summary>
		public override void Cache()
		{
			CachePolicy.Cache(CacheKey, this);
		}

		/// <summary>
		/// Removes the current instance from the cache using the prevalent cache policy.
		/// </summary>
		public override void UnCache()
		{
			CachePolicy.UnCache(CacheKey);
		}

		/// <summary>
		/// This method adds and updates user order line items from the database
		/// using the specified transaction object and the sql command objects. It
		/// creates the command object if its not already created. The operations 
		/// add and update is done through single interface because this class uses the 
		/// data adapter to perform the appropriate operation based on the row state.
		/// </summary>
		/// <param name="transaction">specified transaction object</param>
		/// <param name="args">expects order id in the object array</param>
		public override void Save(SqlTransaction transaction, params object[] args)
		{
			orderID = Convert.ToInt32(args[FirstAgrument]);	
			if(!isCommandCreated)
				CreateCommands();			
			ExecuteUpdate(transaction);
		}

		#endregion

		#region Indexer
		/// <summary>
		/// Gets the UserOrderLineItem object from the line items collection
		/// usiing the specified index.
		/// </summary>
		public UserOrderLineItem this[int index]
		{
			get{return (UserOrderLineItem)lineItems[index];}
		}
		#endregion

		#region IEnumerable Members

		/// <summary>
		/// Returns an enumerator for the arraylist containing the line items.
		/// Supports iteration over UserOrderLineItems
		/// </summary>
		/// <returns>Enumerator for the line items collection</returns>
		public IEnumerator GetEnumerator()
		{
			return lineItems.GetEnumerator();
		}

		#endregion

		#region Private Methods
		/// <summary>
		/// Loads the array list with lineItem objects. This method sorts the default view of the line items,
		/// iteratess over each rowView of the dataView, creates a lineitem object for each row and adds it to
		/// the arraylist storing the line item objects.
		/// </summary>
		private void LoadLineItemList()
		{
			lineItems.Clear();
			DataRowCollection lineItemRows = DataContainer.Tables[Constants.UserOrderDetailsLineItemsTableName].Rows;
			
			foreach(DataRow lineItemRow in lineItemRows)
			    lineItems.Add(new UserOrderLineItem(lineItemRow));
		}

		/// <summary>
		/// Creats a datatable, adds columns to it and adds the datatable to the datacontainer.
		/// This method is necessary because the properties of the individual lineItem class 
		/// have been bound to records contained in a lineitems table of the DataConatiner.
		/// In situation when new line item is created, and its property accessed, it will
		/// throw an error if dataConatiner does not have the line item table.
		/// </summary>
		private void CreateAndAddLineItemsTable()
		{
			DataTable dataTable = new DataTable(Constants.UserOrderDetailsLineItemsTableName);
			
			dataTable.Columns.Add(Constants.LineIDColumnName, typeof(int));
			dataTable.Columns.Add(Constants.OrderIDColumnName, typeof(int));
			dataTable.Columns.Add(Constants.FundIDColumnName, typeof(int));
			dataTable.Columns.Add(Constants.FundNumberColumnName, typeof(string));
			dataTable.Columns.Add(Constants.AccountIDColumnName, typeof(int));
			dataTable.Columns.Add(Constants.AccountNumberColumnName, typeof(string));
			dataTable.Columns.Add(Constants.CommentColumnName, typeof(string));
			dataTable.Columns.Add(Constants.AmountColumnName, typeof(decimal));
			dataTable.Columns.Add(Constants.RequestorColumnName, typeof(string));
			
			DataContainer.Tables.Add(dataTable);
		}

		/// <summary>
		/// Gets the data table from the datacontainer and adds a data row in it.
		/// This method is necessary because the properties of the individual lineItem class 
		/// have been bound to records contained in a lineitems table of the DataConatiner.
		/// In situation when new line item is created, and its property accessed, it will
		/// throw an error if the line item table of the datacontainer has no corresponding row.
		/// </summary>
		private void AddLineItemRow()
		{
			DataTable dataTable = DataContainer.Tables[Constants.UserOrderDetailsLineItemsTableName];
			dataTable.Rows.Add(dataTable.NewRow());
		}	

		/// <summary>
		/// Creates the command objects for the add and update operation. This method
		/// initializes the command objects and adds parameters to the command objects.
		/// </summary>
		private void CreateCommands()
		{
			int domainUserID = ((UserContext)CachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId;

			addCommand = new SqlCommand();
			addCommand.CommandText = AddLineItemSPName;
			addCommand.CommandType = CommandType.StoredProcedure;
			addCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			addCommand.Parameters.Add(DomainUserIDParamName, SqlDbType.Int).Value = domainUserID;
			addCommand.Parameters.Add(LineIDParamName, SqlDbType.Int).SourceColumn = Constants.LineIDColumnName;
			addCommand.Parameters.Add(OrderIDParamName, SqlDbType.VarChar).Value = orderID;
			addCommand.Parameters.Add(FundIDParamName, SqlDbType.Int).SourceColumn = Constants.FundIDColumnName;
			addCommand.Parameters.Add(AccountIDParamName, SqlDbType.Int).SourceColumn = Constants.AccountIDColumnName;
			addCommand.Parameters.Add(RequestorParamName, SqlDbType.VarChar).SourceColumn = Constants.RequestorColumnName;
			addCommand.Parameters.Add(CommentParamName, SqlDbType.VarChar).SourceColumn = Constants.CommentColumnName;
			addCommand.Parameters.Add(AmountParamName, SqlDbType.Money).SourceColumn = Constants.AmountColumnName;

			addCommand.Parameters[LineIDParamName].Direction = ParameterDirection.Output;

			updateCommand = new SqlCommand();
			updateCommand.CommandText = UpdateLineItemSPName;
			updateCommand.CommandType = CommandType.StoredProcedure;
			updateCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			updateCommand.Parameters.Add(DomainUserIDParamName, SqlDbType.Int).Value = domainUserID;
			updateCommand.Parameters.Add(LineIDParamName, SqlDbType.Int).SourceColumn = Constants.LineIDColumnName;
			updateCommand.Parameters.Add(OrderIDParamName, SqlDbType.Int).SourceColumn = Constants.OrderIDColumnName;
			updateCommand.Parameters.Add(FundIDParamName, SqlDbType.Int).SourceColumn = Constants.FundIDColumnName;
			updateCommand.Parameters.Add(AccountIDParamName, SqlDbType.Int).SourceColumn = Constants.AccountIDColumnName;
			updateCommand.Parameters.Add(RequestorParamName, SqlDbType.VarChar).SourceColumn = Constants.RequestorColumnName;
			updateCommand.Parameters.Add(CommentParamName, SqlDbType.VarChar).SourceColumn = Constants.CommentColumnName;
			updateCommand.Parameters.Add(AmountParamName, SqlDbType.Money).SourceColumn = Constants.AmountColumnName;

			isCommandCreated = true;
		}

		/// <summary>
		/// This method adds and updates user order line item from the database using the specified
		/// transaction and the sql command object. It sets the connection property  of the add and
		/// update command object with connection assocoiated with the transaction object. It intializes
		/// the sql adapter and sets its Insert and Update  Command property to add, update command objects
		/// created earlier. The update method on the data  adapter looks into the row state of the specified
		/// row and fires the approperiate add or update command. 
		/// </summary>
		/// <param name="transaction">open connection to database</param>
		private void ExecuteUpdate(SqlTransaction transaction)
		{
			addCommand.Connection = transaction.Connection;
			updateCommand.Connection = transaction.Connection;

			addCommand.Transaction = transaction;
			updateCommand.Transaction = transaction;

			SqlDataAdapter adapter = new SqlDataAdapter();
			adapter.InsertCommand = addCommand;
			adapter.UpdateCommand = updateCommand;

			adapter.Update(DataContainer.Tables[Constants.UserOrderDetailsLineItemsTableName]);
		}	
		#endregion
	}
	#endregion
}
